package com.dao;

import com.vo.Student;
import com.vo.Vote;

import java.sql.*;
import java.util.ArrayList;

public class StudentDao {

    private Connection conn=null;

    /**
     * 初始化数据库
     * @throws Exception
     */
    public void initConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        conn= DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/topiao?allowMultiQueries=true&useUnicode=true&useSSL=false","root","123456");
    }

    //登录
    public Student stuLogin(String studentno,String pwd) throws Exception {
        Student student=null;
        initConnection();
        String sql="select * from t_student where studentno=? and pwd=?";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,studentno);
        ps.setString(2,pwd);
        ResultSet rs=ps.executeQuery();
        while (rs.next()){
            student=new Student();
            student.setStudentno(rs.getString("studentno"));
            student.setStudentname(rs.getString("studentname"));
            student.setPwd(rs.getString("pwd"));
        }
        closeConnection();
        return student;
    }

    //注册
    public void stuRegister(String studentno,String studentname,String pwd) throws Exception {
        initConnection();
        String sql="insert into t_student(studentno,studentname,pwd,sftp) values (?,?,?,0)";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,studentno);
        ps.setString(2,studentname);
        ps.setString(3,pwd);
        ps.executeUpdate();
        closeConnection();
    }


    //返回所有学生
    public ArrayList getAllStudent() throws Exception {
        ArrayList al=new ArrayList();
        initConnection();
        String sql="select * from t_student ";
        Statement stat=conn.createStatement();
        ResultSet rs=stat.executeQuery(sql);
        while (rs.next()){
            Student student=new Student();
            student.setStudentno(rs.getString("studentno"));
            student.setStudentname(rs.getString("studentname"));
            student.setPwd(rs.getString("pwd"));
            al.add(student);
        }
        closeConnection();
        return al;
    }


    //删除学生
    public void deleteStudent(String studentno) throws Exception {
        initConnection();
        String sql="delete from  t_student  where studentno=?";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,studentno);
        ps.executeUpdate();
        closeConnection();
    }

    //新增学生
    public void addStudent(String studentno,String studentname,String pwd) throws Exception {
        initConnection();
        String sql="insert into t_student(studentno,studentname,pwd) values (?,?,?)";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,studentno);
        ps.setString(2,studentname);
        ps.setString(3,pwd);
        ps.executeUpdate();
        closeConnection();
    }

    //修改学生
    public void upStudent(String studentname,String pwd,String studentno) throws Exception {
        initConnection();
        String sql="update t_student set studentname=?,pwd=? where studentno=?";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setString(1,studentname);
        ps.setString(2,pwd);
        ps.setString(3,studentno);
        ps.executeUpdate();
        closeConnection();
    }

    //修改学生投票状态
    public void upStudentSftp(String studentno) throws Exception {
        initConnection();
        String sql="update t_student set sftp=? where studentno=?";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setInt(1,1);
        ps.setString(2,studentno);
        ps.executeUpdate();
        closeConnection();
    }



    private void closeConnection() throws Exception{
        conn.close();
    }


}
